﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using QLZJFurnitureSCM.Model;
using System.Data.SqlClient;
using System.Data.Linq;
using System.Data;

namespace QLZJFurnitureSCM.DAL
{
    public class UserDAO : IBaseDAO<User>
    {
        private SqlConnection sqlConn = null;
        private string connectionString = DALFactory.CONNECTIONSTRING;

        public UserDAO()
        {
            if (sqlConn == null)
                sqlConn = new SqlConnection(connectionString);
        }

        public User Create(User t)
        {
            try
            {
                string query = string.Format("INSERT INTO [Users]([userName],[userPwd],[userRole],[nickName]) values('{0}','{1}',{2},'{3}'); select @@identity;", 
                                                        t.UserName, t.UserPwd, t.UserRole, t.NickName);
                if (sqlConn.State != ConnectionState.Open)
                    sqlConn.Open();
                SqlCommand sqlCmd = new SqlCommand(query, sqlConn);
                Console.WriteLine(query);
                object o = sqlCmd.ExecuteScalar();
                sqlConn.Close();
                if (o != null)
                    t.UserId = int.Parse(o.ToString());
            }
            catch (System.Exception ex)
            {
                Console.WriteLine(ex);
                return Create(t);
            }
            return t;
        }

        public User Read(User t)
        {
            /*
            DataContext dataContext = new DataContext(sqlConnection);
            var enumUser = dataContext.ExecuteQuery<User>("select * from Users where userName = {0} and userPwd = {1};", new object[]{t.UserName, t.UserPwd});
            if (enumUser.Count() != 0)
                t = enumUser.First();
            return t;*/
            /**/
            SqlCommand sqlCmd = new SqlCommand(string.Format("select * from Users where userName = '{0}' and userPwd = '{1}';", t.UserName, t.UserPwd), sqlConn);
            try
            {
                if (sqlConn.State != ConnectionState.Open)
                    sqlConn.Open();
            }
            catch (System.Exception)
            {
                return null;
            }

            SqlDataReader dataReader = sqlCmd.ExecuteReader();

            if (dataReader.HasRows)
            {
                //Invalid attempt to read when no data is present.
                dataReader.Read();
                t.UserId = (int)dataReader["userId"];
                t.UserRole = (int)dataReader["userRole"];
                t.NickName = dataReader["nickName"].ToString();
                sqlConn.Close();
                return t;
            }
            return null;
            //////////////////////////////////////////////////////////////////////////

            /*
            Object o = sqlCmd.ExecuteScalar();
            if (null != o)
                t.UserId = (int)o;
            else
                t = null;
            return t;*/

            /*
            DataContext dataContext = new DataContext(sqlConnection);
            IEnumerable<User> enumUser =dataContext.ExecuteQuery<User>("select * from User where userName = {0} and userPwd = {1};", {})
            */
        }

        public bool Update(User t)
        {
            throw new NotImplementedException();
        }

        public bool Delete(User t)
        {
            DataContext dataContext = new DataContext(connectionString);
            try
            {
                string query = string.Format("Delete from Users where userId = {0};", t.UserId);
                Console.WriteLine("delete query:" + query);
                dataContext.ExecuteQuery<User>(query);
            }
            catch (System.Exception)
            {
                return false;
            }
            return true;
        }

        public List<User> ReadAll()
        {
            DataContext dataContext = new DataContext(connectionString);
            IEnumerable<User> enumUser = dataContext.ExecuteQuery<User>("select * from Users;");
            return enumUser.ToList();
        }

    }
}
